/**
 * @Auther: ChenforCode
 * @Date: 2018/9/11 22:08
 * @Description:
 */
package cn.chenforcode.dao;

import cn.chenforcode.entity.Book;
import cn.chenforcode.entity.Borrow;
import oracle.jdbc.OracleType;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.query.Query;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.hibernate5.SessionFactoryUtils;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.sql.CallableStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;
import java.util.List;

@Repository
@Transactional
public class BorrowDAO {
    @Autowired
    private SessionFactory sessionFactory;

    public Session getSession() {
        return sessionFactory.getCurrentSession();
    }

    /**
     * @return void
     * @Author ChenforCode
     * @Description //TODO 向借书表中插入借书记录
     * @Date 22:10 2018/9/11
     * @Param [borrow]
     **/
    public void borrowBook(Borrow borrow) {
        this.getSession().save(borrow);
    }

    /**
     * @return int
     * @Author ChenforCode
     * @Description //TODO 根据学号和书号判断这次借书是否能成功
     * @Date 22:11 2018/9/11
     * @Param [sno, bno]
     **/
    public int queryCanBorrow(String sno, String bno) {
        CallableStatement statement = null;
        int retValue = 0;
        try {
            statement = SessionFactoryUtils.getDataSource(sessionFactory)
                    .getConnection().prepareCall("{call querycanbor(?,?,?)}");
            statement.setString(1, sno);
            statement.setString(2, bno);
            statement.registerOutParameter(3, OracleType.NUMBER);
            statement.execute();

            retValue = statement.getInt(3);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            return retValue;
        }
    }

    /**
     * @return void
     * @Author ChenforCode
     * @Description //TODO 还书
     * @Date 23:29 2018/9/11
     * @Param [sno, Bno]
     **/
    public void returnBook(Borrow borrow) {
        this.getSession().update(borrow);
    }

    /**
     * @return int
     * @Author ChenforCode
     * @Description //TODO 判断是否可以还书
     * @Date 23:35 2018/9/11
     * @Param [sno]
     **/
    public int queryCanReturnBook(String sno, String bno) {
        CallableStatement statement = null;
        int retValue = 0;
        try {
            statement = SessionFactoryUtils.getDataSource(sessionFactory)
                    .getConnection().prepareCall("{call querycanret(?,?,?)}");
            statement.setString(1, sno);
            statement.setString(2, bno);
            statement.registerOutParameter(3, OracleType.NUMBER);
            statement.execute();

            retValue = statement.getInt(3);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            return retValue;
        }
    }

    /**
     * @return java.util.List<cn.chenforcode.entity.Borrow>
     * @Author ChenforCode
     * @Description //TODO 获取所有的借书记录
     * @Date 23:38 2018/9/11
     * @Param []
     **/
    public List<Borrow> getAllBorrows() {
        return this.getSession().createQuery("from Borrow").list();
    }

    /**
     * @return java.util.List<cn.chenforcode.entity.Borrow>
     * @Author ChenforCode
     * @Description //TODO 通过学生学号查询该学生的借书记录
     * @Date 23:42 2018/9/11
     * @Param [sno]
     **/
    public List<Borrow> getBorrowBySno(String sno) {
        return this.getSession().createQuery("from Borrow where sno = ?1")
                .setParameter(1, sno).list();
    }

    /**
     * @return java.util.List<cn.chenforcode.entity.Borrow>
     * @Author ChenforCode
     * @Description //TODO 通过书籍的书号查询这个书的借还记录
     * @Date 23:42 2018/9/11
     * @Param [bno]
     **/
    public List<Borrow> getBorrowByBno(String bno) {
        return this.getSession().createQuery("from Borrow where bno = ?1")
                .setParameter(1, bno).list();
    }

    /**
     * @return java.util.List<cn.chenforcode.entity.Borrow>
     * @Author ChenforCode
     * @Description //TODO 查询学号和书号查询这个学生对这本书籍的借阅记录
     * @Date 23:47 2018/9/11
     * @Param [sno, bno]
     **/
    public List<Borrow> getBorrowBySnoAndBno(String sno, String bno) {
        Query query = this.getSession().createQuery("from Borrow where sno = ?1 and bno = ?2");
        query.setParameter(1, sno);
        query.setParameter(2, bno);
        return query.list();
    }

    public Borrow getBorrowHaveNoReturn(String sno, String bno) {
        Query query = this.getSession().createQuery("from Borrow where sno = ?1 and bno = ?2 and retdate is null");
        query.setParameter(1, sno);
        query.setParameter(2, bno);
        return (Borrow) query.uniqueResult();
    }

    public List<Borrow> getBorrowHaveNoReturn(String sno) {
        return this.getSession().createQuery("from Borrow where sno = ?1 and retdate is null")
                .setParameter(1, sno).list();
    }

}
